﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Configuration;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web;

public class ServerObject
{
    #region Logger Setup
    protected static readonly log4net.ILog msLogger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
    #endregion Logger Setup

    #region Data members
    string mLastError = string.Empty;
    DataBridge mDataBridge = null;

    DataTable mUserData = null;

    string mUser = string.Empty;
    int mUserId = 0;
    #endregion Data members

    #region Properties
    public DataTable UserData
    {
        get { return mUserData; }
    }

    public string LastError
    {
        get { return mLastError; }
    }

    public int UserID
    {
        get 
        {
            int userID = 0;
                
            if (HttpContext.Current.Session["UserData"] != null)
            {
                DataTable userData = (DataTable)HttpContext.Current.Session["UserData"];

                userID = (int)userData.Rows[0]["ID"];
            }
            return userID;
        }
    }
    
    public int UserLanguageID
    {
        get 
        {
            int languageCode = 20;

            if (HttpContext.Current.Session["UserData"] != null)
            {
                DataTable userData = (DataTable)HttpContext.Current.Session["UserData"];

                if (userData != null && userData.Rows.Count == 1 && userData.Columns.Contains("LanguageID") && !userData.Rows[0]["LanguageID"].ToString().Equals(string.Empty))
                {
                    int.TryParse(userData.Rows[0]["LanguageID"].ToString(), out languageCode);
                }
            }

            return languageCode;
        }
    }

    public bool isLogetUser
    {
        get
        {
            bool logget = false;

            if (HttpContext.Current.Session["UserData"] != null)
            {
                DataTable userData = (DataTable)HttpContext.Current.Session["UserData"];
                int UserID = 0;
                UserID = (int)userData.Rows[0]["ID"];
                if (UserID != 0)
                {
                    logget = true;
                }
            }
            return logget;
        }
    }


    #endregion Properties
    
    #region Construction and destruction
    public ServerObject()
    {   
        mDataBridge = new DataBridge();          
    }
  
    ~ServerObject()
    {
        mDataBridge = null;
    }
    #endregion Construction and destruction

    public void InfoText(System.Web.UI.Page inputPage, string inputText)
    {
        string infoText = string.Empty;

        Label infoLabelMaster = (Label)inputPage.Master.FindControl("infoLabelMaster");
        if (infoLabelMaster != null)
        {
            infoLabelMaster.Text = inputText;
        }     
    }

    public string CreateEncodedString(string inptString)
    {
        CryptorEngine criptor = new CryptorEngine();

        string result = string.Empty;

        result = criptor.Encrypt(inptString.Trim(), true);

        return result;
    }

    public string DecodeCriptedString(string inptString)
    {
        CryptorEngine criptor = new CryptorEngine();

        string result = string.Empty;

        result = criptor.Decrypt(inptString.Trim(), true);

        return result;
    }

    public void fillFrontPageSelector(DropDownList destinationDDl, DataTable inputTable, string DataTextField, string DataValueField)
    {
        if (destinationDDl != null && inputTable != null)
        {
            destinationDDl.DataSource = null;

            destinationDDl.DataSource = inputTable;
            destinationDDl.DataValueField = DataValueField;
            destinationDDl.DataTextField = DataTextField;
            destinationDDl.DataBind();
            destinationDDl.SelectedIndex = -1;
        }
    }

    public DataTable ChekExistRegistredEmail(string inputEmail)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "select * from Solicitanti where email= '" + inputEmail + "' ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetJudeteList(int idTara)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT * FROM tari_judete "
                                + " Where IdParent = " + idTara
                                + " ORDER BY DenumireJudet ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetCountryList()
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT * FROM tari_judete "
                    + " WHERE IdParent is NULL OR IdParent = 0 "
                                + "ORDER BY DenumireJudet ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }
    
    public void fillSelector(DropDownList destinationDDl, DataTable inputTable, string DataTextField, string DataValueField)
    {
        if (destinationDDl != null && inputTable != null)
        {
            destinationDDl.DataSource = null;

            //inputTable.DefaultView.Sort = DataValueField + " ASC";
            destinationDDl.DataSource = inputTable;
            destinationDDl.DataValueField = DataValueField;
            destinationDDl.DataTextField = DataTextField;
            destinationDDl.DataBind();
        }
        else
        {
            destinationDDl.DataSource = null;
            destinationDDl.DataBind();
        }
    }
    
    #region Logs
    public void LogError(string message)
    {
        if (!string.IsNullOrEmpty(message.Trim()))
        {
            msLogger.Error(message);
        }
    }

    public void LogDebug(string message)
    {
        if (!string.IsNullOrEmpty(message.Trim()))
        {
            msLogger.Debug(message);
        }
    }

    public void LogFatal(string message)
    {
        if (!string.IsNullOrEmpty(message.Trim()))
        {
            msLogger.Fatal(message);
        }
    }

    public void LogWarming(string message)
    {
        if (!string.IsNullOrEmpty(message.Trim()))
        {
            msLogger.Warn(message);
        }
    }

    public void LogInfo(string message)
    {
        if (!string.IsNullOrEmpty(message.Trim()))
        {
            msLogger.Info(message);
        }
    }

    public string WriteLogContent(System.Web.UI.Page inputPage)
    {
        string contentString = string.Empty;
        try
        {
            System.Xml.XmlElement log4netSection = (System.Xml.XmlElement)ConfigurationManager.GetSection("log4net");
            System.Xml.XmlNodeList nodeList = log4netSection.ChildNodes;
            bool breakCycle = false;
            foreach (System.Xml.XmlNode node in nodeList)
            {
                if (node.Name.Equals("appender"))
                {
                    System.Xml.XmlNodeList childNodeList = node.ChildNodes;
                    foreach (System.Xml.XmlNode childNode in childNodeList)
                    {
                        if (childNode.Name.Equals("file"))
                        {
                            System.Xml.XmlAttribute valueAttribute = childNode.Attributes["value"];
                            string filePath = valueAttribute.Value;
                            string fileFullPath = inputPage.Server.MapPath(filePath);

                            System.IO.FileStream fs = null;

                            try
                            {
                                fs = new System.IO.FileStream(fileFullPath, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite);
                                if (fs.CanRead)
                                {
                                    byte[] fileBuffer = new byte[fs.Length];
                                    char[] fileChars = new char[fs.Length];

                                    fs.Read(fileBuffer, 0, (int)fs.Length);

                                    for (int i = 0; i < fileBuffer.Length; i++)
                                    {
                                        char aChar = (char)fileBuffer[i];
                                        fileChars[i] = aChar;
                                    }

                                    contentString = new String(fileChars, 0, fileChars.Length);
                                    contentString = contentString.Replace("DATE:", "<br />DATE:");
                                    //contentString = contentString.Replace("DATE:", "<tr><td>");
                                    //contentString = contentString.Replace("LEVEL:", "</td><td>");
                                    //contentString = contentString.Replace("LOGGER:", "</td><td>");
                                    //contentString = contentString.Replace("MESSAGE:", "</td><td>");
                                    //contentString = contentString.Replace("USERIP:", "</td><td>");
                                    //contentString = contentString.Replace("\r\n", "</td></tr>");
                                    //contentString = contentString.Insert(0, "<table border=\"1px\"> <tr><td>DATE</td><td>LEVEL</td><td>LOGGER</td><td>MESSAGE</td><td>CLIENT IP</td></tr> ");
                                    //contentString += "</table>";
                                }
                                else
                                {
                                    this.InfoText(inputPage, "Can NOT read " + fs.Name + "!");
                                }
                            }
                            catch (Exception fileException)
                            {
                                //msLogger.Fatal("Exception reading log file.");
                                this.InfoText(inputPage, "Exception reading log file. " + fileException.Message);
                            }
                            finally
                            {
                                if (fs != null)
                                {
                                    fs.Close();
                                }
                            }


                            breakCycle = true;
                            break;
                        }
                    }
                }

                if (breakCycle)
                {
                    break;
                }
            }

        }
        catch (Exception ex)
        {
            contentString = "Exception reading log file!!! " + ex.Message + "<br/><br/>";
        }

        return contentString;
    }
    #endregion Logs
   
    public bool LoginBackEnd(string login, string password)
    {
        bool result = false;
        mLastError = string.Empty;
        string encryptPassword = Utils.GetMd5Hash(password);
        try
        {
            if (login.Contains("'") || login.Contains(" ") || password.Contains("'") || password.Contains(" "))
            {
                throw new Exception("Invalid characters in login or password");
            }

            if (mDataBridge.Connect() == true)
            {   
                string query = "SELECT * "
                    + "FROM users "
                    + "WHERE login = '" + login + "' "
                    + "AND password = '" + encryptPassword + "' "
                    + " ";

                DataTable users = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (users != null && users.Rows.Count == 1)
                {
                    if (string.IsNullOrEmpty(mUser) && mUserId == 0)
                    {
                        mUser = users.Rows[0]["login"].ToString();
                        mUserId = int.Parse(users.Rows[0]["userid"].ToString());

                        mUserData = users;

                        result = true;
                    }
                }
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }
    
    public bool LoginFrontEnd(string login, string password)
    {
        bool result = false;
        mLastError = string.Empty;
        
        string encryptedPassword = Utils.GetMd5Hash(password);

        try
        {
            if (login.Contains("'") || login.Contains(" ") || password.Contains("'") || password.Contains(" "))
            {
                throw new Exception("Invalid characters in login or password");
            }
            

            if (mDataBridge.Connect() == true)
            {
                string query = @"SELECT *, -1 as roletype, Email as login
                        ,(CASE when JudetDomiciliuID is null OR JudetDomiciliuID = 0
                        THEN
	                        (select IdConsulat From Tari_judete Where IdJudet = Solicitanti.TaraDomiciliuId)
                        ELSE
	                        (select IdConsulat From Tari_judete Where IdJudet = Solicitanti.JudetDomiciliuID) 
                        END "
                        + " ) as \"PreferedIdConsulat\" " 

                    + ",(CASE when JudetDomiciliuID is null OR JudetDomiciliuID = 0 "
                    + " THEN "
                    + "    (Select Denumire From Consulate Where IdConsulat = (select IdConsulat From Tari_judete Where IdJudet = Solicitanti.TaraDomiciliuId)) "
                    + " ELSE "
                    + "    (Select Denumire From Consulate Where IdConsulat = (select IdConsulat From Tari_judete Where IdJudet = Solicitanti.JudetDomiciliuID))  "
                    + " END "
                        + " ) as \"PreferedDenumireConsulat\" " 

                        + " FROM Solicitanti "
                        + " WHERE Email = '" + login + "' "
                        + " AND parola = '" + encryptedPassword + "' "
                        + " AND ParentID = 0 "
                        + " AND isActive = 1 "
                        + " ";

                DataTable users = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (users != null && users.Rows.Count == 1)
                {
                    if (string.IsNullOrEmpty(mUser) && mUserId == 0)
                    {
                        mUser = users.Rows[0]["Email"].ToString();
                        mUserId = int.Parse(users.Rows[0]["ID"].ToString());

                        mUserData = users;

                        result = true;
                    }
                }
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }
    
    public string GetPanelName(string panelGUID)
    {
        string result = string.Empty;
        try
        {
            //todo generate GUID random, not static
            //DataTable panelTable = GetDataTableGUID();
            switch (panelGUID)
            {
                case "0d95f099-2e70-444a-8cc1-e9d5e6504b1c":
                    result = "usersAdminPanel";
                    break;
                case "b430fa20-65b1-4f19-a3ee-ee8520b988e0":
                    result = "addUserPanel";
                    break;

                case "113d80fd-326e-4667-9986-05bd838b4935":
                    result = "editUserPanel";
                    break;

                case "160f686a-b00b-4308-afa7-ad5f5aeec340":
                    result = "editUserPasswordPanel";
                    break;

                case "ba52f4c7-aa81-44d0-86af-427f18caa621":
                    result = "logsPanel";
                    break;

                case "8aa20326-3c52-462b-93c7-e276b80c0b9b":
                    result = "ConsulatePanel";
                    break;

                case "93cb7b1f-8cdf-4786-9e4e-7071b4fe8c2a":
                    result = "addNewConsulatPanel";
                    break;

                case "75b82823-d432-42fb-bcc1-0604a34152e8":
                    result = "editConsulatPanel";
                    break;

                case "19e6f322-95d6-4601-99d8-b781631adc2c":
                    result = "servicesListPanel";
                    break;

                case "84940fbd-b0dd-448b-a6ad-73aad5e7a8ae":
                    result = "AddNewSubcategoryServicePanel";
                    break;

                case "e2079510-0ae0-4b6a-a98b-e6e5d2a5804f":
                    result = "editSubcategoryServicePanel";
                    break;

                case "b918b64c-6a2d-49ca-81f3-11aef7faf247":
                    result = "templateZIPanel";
                    break;

                default:
                    result = string.Empty;
                    break;
            }
            //be255fb4-fb6e-4fb3-ab5f-3e833b3d29a0
            //45cf5f7e-2371-408d-b7f3-5ef9b7ab91af
            //3e0d41b8-9e80-4f43-83b4-6cc93566a0c2
            //0ab6e1c7-0222-4e47-aed1-c8fc8f8a2318
            //cedbf5ec-8c97-4ab3-b249-e893abcce179
            //e9a1db56-9ad2-4c71-aeec-0305ed320e1f
            //64b84887-55d2-455e-861a-6d332bee3a35

        }
        catch
        {
            return string.Empty;
        }

        return result;
    }

    public string GetAreaName(string areaGUID)
    {
        string result = string.Empty;

        try
        {
            //todo generate GUID random, not static
            //Online GUID Generator www.guidgenerator.com
            //DataTable areaTable = GetDataTableGUID();
            switch (areaGUID)
            {
                case "f70afd7a-0e98-4cde-b83e-fe7be4ee7399":
                    result =  "users";
                    break;

                case "90df5a36-86e2-4d2a-8e15-60ce225b7f6b":
                    result = "consulatList";
                    break;

                case "924fb833-b129-4c3a-8c76-2b6ac8ee87ee":
                    result = "servicesLst";
                    break;

                case "50c56f4e-c1ad-4155-9585-2c7bf8245ed2":
                    result = "templatezi";
                    break;

                default:
                    result = string.Empty;
                    break;
            }           

                //d2dabdc5-8ae5-47f4-9507-52ddb1258814
                //75fc91e2-b89d-4308-9ad7-308c0473ff7b
                //cdca3cea-0674-43de-8302-f2481e93ba42
                //d8221407-70a5-4299-aa8e-fad01ebf85d0
                //b78a5103-fe30-4329-b8d6-b76e2aec3612
                //83d49a31-83e0-4542-9536-e4c09a59583b
                //b9393f9e-da67-4da4-9b31-be3d9a63681d
                //f1b9e71d-b1cb-4b93-94bf-0d27bffe1c7f
                //509ac601-beaf-4dfa-bb50-f5a82fde2871
                //54b8221c-cb3b-4685-9b5f-d40e16edf266
                //cb751bcf-e5c7-43a6-8742-38cdf5ae2ba3
                //dbd078a3-8d47-4f3b-97af-99b4554d6e50
                //511d87c0-d060-4e53-84d2-1c7f3cfb4ce4
                //71bb2876-94fc-4418-85f0-6d6bcfc8f71b
                //2ce3f91f-2af9-40ef-ad9d-6b4c154cff4b
                //ef16afa2-3b9e-416c-8c18-8038924d8591
            

        }
        catch
        {
            return string.Empty;
        }            
        
        return result;
    }

    //DataTable GetDataTableGUID()
    //{
    //    DataTable resultTable = new DataTable();
    //    resultTable.Columns.Add("ColumnName", typeof(string));
    //    resultTable.Columns.Add("ColumnGUID", typeof(string));
    //    resultTable.NewRow();

    //    return resultTable;
    //}

    #region calssifiers and Classifiers Types

    public DataTable GetClassifierTypesList()
    {
        DataTable classifierTypesList = new DataTable();

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string commandText = "SELECT   ClassifierType.TypeID AS \"Type ID\" "
                                             + " , ClassifierType.Name AS Name " 
                                             + " , (Select ClassifierTypeParent.Name FROM ClassifierType as ClassifierTypeParent WHERE ClassifierTypeParent.TypeID = ClassifierType.ParentID) AS \"Parent Name\" "
                                             + " , ParentID "
                                             + "  FROM ClassifierType  order BY Name";

                classifierTypesList = mDataBridge.ExecuteQuery(commandText); // PG compliant
                mLastError = mDataBridge.LastError;
            }
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return classifierTypesList;
    }

    public bool NewClassifierTypes(string clTypeDescription, int parentID)
    {
        bool result = false;      
        try
        {
            if (mDataBridge.Connect() == true)
            {
                string nonQuery = @"INSERT INTO ClassifierType (Name, ParentID)"
                                       + " VALUES ( '" + clTypeDescription + "', "+parentID+" )";

                result = mDataBridge.ExecuteNonQuery(nonQuery); // PG compliant
                mLastError = mDataBridge.LastError;
            }
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool UpdateClassifierTypes(int clTypeID, string clTypeDescription, int parentID)
    {
        bool result = false;
        try
        {
            if (mDataBridge.Connect() == true)
            {
                string nonQuery = @"Update ClassifierType Set "
                    + " Name = '" + clTypeDescription + "' "
                    + ", ParentID = " + parentID + " "
                    + " WHERE TypeID = " + clTypeID;

                result = mDataBridge.ExecuteNonQuery(nonQuery); // PG compliant
                mLastError = mDataBridge.LastError;
            }
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetAllClassifiers(int clTypeID)
    {     
        DataTable classifiersList = new DataTable();

        try
        {
            if (mDataBridge.Connect() == true)
            {

                string commandText = "SELECT Classifiers.TypeID AS \"Type ID\" "
                                        + ", Classifiers.Code AS Code "
                                        + ", Classifiers.Name as \"Name\" "  
                                        + ", (SELECT ClassifiersParent.Name FROM Classifiers AS ClassifiersParent WHERE ClassifiersParent.Code = Classifiers.ParentID) AS \"Parent Name\" "
                                        + ", Classifiers.ParentID "
                                        + " FROM Classifiers as Classifiers "
                                        + " WHERE Classifiers.TypeID = " + clTypeID + " "
                                        + " ORDER BY Classifiers.TypeID, Classifiers.Code "
                                        + " ";

                classifiersList = mDataBridge.ExecuteQuery(commandText); // PG compliant
                mLastError = mDataBridge.LastError;
            }
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return classifiersList;
    }

    public bool UpdateClassifier(int classifierCode, string clTypeDescription)
    {
        bool result = false;
        try
        {
            if (mDataBridge.Connect() == true)
            {
                string nonQuery = @"Update Classifiers Set Name = '" + clTypeDescription + "'  WHERE Code = " + classifierCode;

                result = mDataBridge.ExecuteNonQuery(nonQuery); // PG compliant
                mLastError = mDataBridge.LastError;
            }
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool NewClassifier(int clTypeID, string clTypeDescription, int parentID)
    {
        bool result = false;
        try
        {
            if (mDataBridge.Connect() == true)
            {
                string nonQuery = @"INSERT INTO Classifiers (TypeID, Name, ParentID)"
                                       + " VALUES ( " + clTypeID + ", '" + clTypeDescription + "', "+parentID+" )";

                result = mDataBridge.ExecuteNonQuery(nonQuery); // PG compliant
                mLastError = mDataBridge.LastError;
            }
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetClassifiersByParentID(int parentID)
    {
        DataTable classifiersList = new DataTable();
        
        try
        {
            if (mDataBridge.Connect() == true)
            {
                if (parentID != 0)
                {
                    string commandText = "SELECT Classifiers.TypeID AS \"Type ID\" "
                                            + ", Classifiers.Code AS Code "
                                            + ", Classifiers.Name as \"Name\" "
                                            + ", (SELECT ClassifiersParent.Name FROM Classifiers AS ClassifiersParent WHERE ClassifiersParent.Code = Classifiers.ParentID) AS \"Parent Name\" "
                                            + ", Classifiers.ParentID "
                                            + " FROM Classifiers as Classifiers "
                                            + " WHERE Classifiers.ParentID = " + parentID + " "
                                            + " ORDER BY Classifiers.TypeID, Classifiers.Code "
                                            + " ";

                    classifiersList = mDataBridge.ExecuteQuery(commandText); // PG compliant
                    mLastError = mDataBridge.LastError;
                }
            }
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return classifiersList;
    }

    #endregion calssifiers and Classifiers Types

    #region USERS
    public DataTable GetSystemUsers(int idConsulat)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT * "
                        + ", (Select Denumire FROM Consulate as Consulate Where Consulate.IdConsulat = users.IdConsulat) as \"Consulatul\" "
                        + ", (SELECT Classifiers.Name FROM Classifiers WHERE Classifiers.Code = users.passwordstatus ) AS password_status_name "
                        + ", CASE WHEN recordstatus IS NULL OR recordstatus = 0   THEN 'Disabled' ELSE 'Active' END  AS record_status_name "
                        + ", (SELECT UsersList.login FROM Users as UsersList WHERE UsersList.userid = Users.edituserid ) AS edituser_login "
                        + ", (SELECT Classifiers.Name FROM Classifiers WHERE Classifiers.Code = users.roletype ) AS role_type_name "
                        + " FROM users "
                        + " Where idConsulat = " + idConsulat + " "
                        + " ORDER BY Nume, Prenume ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool AddNewSysemUser(int idConsulat, string userNume, string userPrenume, string login, string password, int roleType, int passwordStatus, int recordStatus, int editUserID,  string eMail)
    {
        bool result = false;
        mLastError = string.Empty;
        string encryptPassword = Utils.GetMd5Hash(password);
        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "INSERT INTO USERS (IdConsulat, Nume, Prenume, login, password, roleType, passwordstatus, recordStatus, editUserID, editDate, email) Values ("
                        + "  " + idConsulat + " "
                        + " , '" + userNume + "' "
                        + " , '" + userPrenume + "' "
                        + " , '" + login + "' "
                        + " , '" + encryptPassword + "' "
                        + " , " + roleType + " "
                        + " , " + passwordStatus + " "
                        + " , " + recordStatus + " "
                        + " , " + editUserID + " "
                        + " , @editDate "
                        + " , '" + eMail + "' "
                        + " )";

                Hashtable parameters = new Hashtable();
                parameters.Add("@editDate", DateTime.Now);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool UpdateSysemUser(int userID, int idConsulat, string userNume, string userPrenume, string login, int roleType, int passwordStatus, int recordStatus, int editUserID, string eMail)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "Update USERS SET "
                        + " IdConsulat = " + idConsulat + " "
                        + " , Nume = '" + userNume + "' "
                        + " , Prenume = '" + userPrenume + "' "
                        + " , login = '" + login + "'  "
                        //+ " , password = '" + password + "' "
                        + " , roleType = " + roleType + " "
                        + " , passwordstatus = " + passwordStatus + " "
                        + " , recordStatus = " + recordStatus + " " 
                        + " , editUserID = " + editUserID + " "
                        + " , editDate = @editDate "
                        + " , email = '" + eMail + "' "
                        + " WHERE userID = " + userID + "";

                Hashtable parameters = new Hashtable();
                parameters.Add("@editDate", DateTime.Now);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool UpdateSysemUserPassword(int userID, string password, int editUserID)
    {
        bool result = false;
        mLastError = string.Empty;
        string encryptPassword = Utils.GetMd5Hash(password);
        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "Update USERS SET "
                        + "  password = '" + encryptPassword + "' "
                        + " , editUserID = " + editUserID + " "
                        + " , editDate = @editDate "
                        + " WHERE userID = " + userID + "";

                Hashtable parameters = new Hashtable();
                parameters.Add("@editDate", DateTime.Now);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetSolicitantiList()
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT * "
                        + " , (Select CL.Name From Classifiers as CL Where Cl.Code = TaraNastereID) as \"stringTaraNastere\" "
                        + " , (Select CL.Name From Classifiers as CL Where Cl.Code = SexID) as \"stringSex\" "
                        + " , (Select CL.Name From Classifiers as CL Where Cl.Code = TaraDomiciliuID) as \"stringTaraDomiciliu\" " 
                        + " FROM Solicitanti "
                        + " ORDER BY Nume, Prenume ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool UpdateSolicitantPassword(int userID, string password)
    {
        bool result = false;
        mLastError = string.Empty;

        string encryptPassword = Utils.GetMd5Hash(password);

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "Update Solicitanti SET "
                        + "  parola = '" + encryptPassword + "' "                       
                        + " WHERE ID = " + userID + "";

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public int GetSolicitantIdByEmail(string email)
    {
        int result = 0;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "Select ID FROM Solicitanti "
                        + " WHERE email = '" + email + "'";

                DataTable tdTable = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (tdTable != null && tdTable.Rows.Count == 1)
                {
                    int.TryParse(tdTable.Rows[0][0].ToString(), out result);
                }
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    #region CreateRandomPassword
    public string CreateRandomPassword(int passwordLength)
    {
        string allowedChars = "abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ0123456789!@$?_-";
        char[] chars = new char[passwordLength];
        Random rd = new Random();

        for (int i = 0; i < passwordLength; i++)
        {
            chars[i] = allowedChars[rd.Next(0, allowedChars.Length)];
        }

        return new string(chars);
    }
    #endregion CreateRandomPassword

    #endregion USERS
    
    #region Consulate

    public DataTable GetConsulatInfoByID(int idConsulat)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = @"select * from  Consulate 
                    where idConsulat = " + idConsulat + " ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetConsulatListByCountryID(int idTara)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = @"select * from  Consulate 
                    where idConsulat = (select idConsulat FROM Tari_Judete WHERE IdJudet = " + idTara + ") ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetConsulatList()
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT "
                        + " IdConsulat as \"ID Consulat\" "
                        + " , Denumire as \"Denumire\" "
                        + " , Adresa as \"Adresa\" "
                        + " , idTara as \"IDTara\"  "
                        + " , (Select DenumireJudet from Tari_Judete WHERE idJudet = idTara) as \"Tara\" "
                        + " , Telefon as \"Telefon\" "
                        + " , Email as \"Email\" "
                        + " , Fax as \"Fax\" "
                        + " , Web as \"Web\" "

                        + "FROM Consulate "
                        + "ORDER BY Denumire ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool UpdateConsulat(int idConsulat, int idTara, string denumire, string adresa, string telefon, string email, string fax, string webPage)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "UPDATE Consulate SET "
                        + "  Denumire = '" + denumire + "' "
                        + " , Adresa = '" + adresa + "' "
                        + " , idTara = '" + idTara + "' "
                        + " , telefon = '" + telefon + "' "
                        + " , email = '" + email + "' "
                        + " , fax = '" + fax + "' "
                        + " , web = '" + webPage + "' "
                        + "Where IdConsulat =  " + idConsulat;

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool AddNewConsulat(int idTara, string denumire, string adresa, string telefon, string email, string fax, string webPage)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "INSERT INTO Consulate (idTara, Denumire, Adresa, telefon, email, fax, web) Values ("
                        + " , " + idTara + " "
                        + " , '" + denumire + "' "
                        + " , '" + adresa + "' "
                        + " , '" + telefon + "' "
                        + " , '" + email + "' "
                        + " , '" + fax + "' "
                        + " , '" + webPage + "' "
                        + " )";

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    #endregion Consulate
    
    #region Services
    public DataTable GetServicesCategoryList()
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT "
                        + " IdService as \"ID Service\" "
                        + " , Denumirea as \"Denumirea\" "
                        + " FROM Services "
                        + " WHERE ParentIdService = 0 "
                        + "ORDER BY Denumirea ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetServiceByID(int idService)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT * "
                        + " FROM Services "
                        + " WHERE IdService =  " + idService.ToString();

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetServicesList(int partentID)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;      

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT "
                        + " IdService as \"ID Service\" "
                        + " , Denumirea as \"Denumirea\" "
                        + " FROM Services "
                        + " WHERE ParentIdService =  " + partentID                      
                        + " ORDER BY Denumirea ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool AddNewServiceCategory(string denumire)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "INSERT INTO Services (ParentIdService, Denumirea) Values ("
                        + " 0 "
                        + " , '" + denumire + "' )";

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool AddNewService(int category, string denumire)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "INSERT INTO Services (ParentIdService, Denumirea) Values ("
                        + " " + category + " "
                        + " , '" + denumire + "' )";

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool UpdateService(int idService, int idCategory, string denumire)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "UPDATE Services SET "
                        + "  Denumirea = '" + denumire + "' "
                        + " , ParentIdService = " + idCategory.ToString()
                        + " Where IdService =  " + idService.ToString();

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool DeleteService(int idService)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "Delete From Services "                       
                        + " Where IdService =  " + idService.ToString();

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool DeleteServiceCategory(int idServiceCategory)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "Delete From Services "
                        + " Where ParentIdService =  " + idServiceCategory.ToString();

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;

                query = "Delete From Services "
                        + " Where IdService =  " + idServiceCategory.ToString();

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    #endregion Services

    #region Template ZI
    public DataTable GetTemplatesList(int idConsulat, int idService, int ziSaptamina)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT "
                        + " TZ.IdTemplate as \"ID Template\" "
                        + " , TZ.IdConsulat as \"Id Consulat\"  "
                        + " ,  (Select CON.Denumire From Consulate as CON Where CON.IdConsulat = TZ.IdConsulat)  as \"Consulat\"  "
                        + " , TZ.ZiSaptamina as \"Zi Saptamina\"  "
                        + " , TZ.IdService as \"ID Service\" "
                        + " , (Select Denumirea From Services WHERE IdService = TZ.IdService) as \"Service\" "
                        + " , TZ.StartHour as \"Start Hour\" "
                        + " , TZ.EndHour as \"End Hour\" "
                        + " , TZ.MaxPerson as \"Max Person\" "
                        + " FROM TemplateZi as TZ "
                        + " WHERE TZ.IdConsulat = " + idConsulat.ToString()
                        + (ziSaptamina != 0 ? " AND TZ.ziSaptamina = " + ziSaptamina : string.Empty)
                        + (idService != 0 ? " AND TZ.IdService = " + idService : string.Empty)
                        + " ORDER BY ZiSaptamina, StartHour, EndHour ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetTemplateZiByID(int idTemplate)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT * "
                        + " FROM TemplateZi as TZ "
                        + " WHERE TZ.IdTemplate = " + idTemplate.ToString();                       

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool AddNewTemplateZi(int idConsulat, int ziSaptamina, int iDService, string startHour, string endHour, int maxPerson)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "INSERT INTO TemplateZi (IdConsulat, ZiSaptamina, IdService, StartHour, EndHour, MaxPerson) "
                        + " Values ("
                        + " " + idConsulat + " "
                        + " ," + ziSaptamina + " "
                        + " ," + iDService + " "
                        + " , '" + startHour + "' "
                        + " , '" + endHour + "' "
                        + " , " + maxPerson + " "
                        + " )";
                
                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool UpdateTemplateZi(int idTemplate, int idConsulat, int ziSaptamina, int iDService, string startHour, string endHour, int maxPerson)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "UPDATE TemplateZi SET "
                        + " IdConsulat = " + idConsulat + " "
                        + " , ZiSaptamina = " + ziSaptamina + " "
                        + " , IdService = " + iDService + " "
                        + " , StartHour = '" + startHour + "' "
                        + " , EndHour = '" + endHour + "' "
                        + " , MaxPerson = " + maxPerson + " "
                        + " WHERE IdTemplate = " + idTemplate.ToString();

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetTemplateByCategory(int idConsulat, int idCategory, int ziSaptamina)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT "
                        + " TZ.IdTemplate as \"ID Template\" "
                        + " , 'Orele: ' + CAST(TZ.StartHour as nvarchar) + ' - ' +  CAST(TZ.EndHour as nvarchar) + ';  Max Pers: ' +  CAST(TZ.MaxPerson  as nvarchar) as   \"DDL Display Text\"  "

                        + " FROM TemplateZi as TZ "
                        + " WHERE TZ.IdConsulat = " + idConsulat
                        + " AND TZ.IdService = " + idCategory + "  "
                        + " AND TZ.ZiSaptamina = " + ziSaptamina;

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool DeleteTemplateZi(int idTemplate)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "Delete From TemplateZi "
                        + " WHERE TemplateZi.idTemplate = " + idTemplate;

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetDefaultTemplatesList()
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT DISTINCT idPriod "
                       + " FROM DefaultTemplateZi  ";                       

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool CheckIfConsulatNotHaveTemplateZi(int idConsulat)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "Select * FROM TemplateZi "
                        + " WHERE idConsulat = " + idConsulat;

                DataTable tableResult = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (tableResult == null || tableResult.Rows.Count == 0)
                {
                    result = true;
                }
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result; 
    }

    public bool InsertDefaultTemplates(string idPeriod, int idConsulat)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "INSERT INTO TemplateZi (IdConsulat, ZiSaptamina, IdService, StartHour, EndHour, MaxPerson) "
                    + " SELECT "
                    + " " + idConsulat + " "
                    + " ,ZiSaptamina "
                    + " ,IdService "
                    + " ,StartHour "
                    + " ,EndHour "
                    + " ,MaxPerson "
                    + " FROM DefaultTemplateZi "
                    + " Where idPriod = '" + idPeriod + "'";

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    #endregion Template ZI

    #region CALENDAR

    public DataTable GetCalendarList(int idConsulat, DateTime dataFrom, DateTime dataTo)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;
        DateTime emptyData = new DateTime(1900,1,1);

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT "
                        + " CL.IDCalendar as \"ID Calendar\" "
                        + " , CL.IdConsulat as \"Id Consulat\"  "
                        + " , (Select CON.Denumire From Consulate as CON Where CON.IdConsulat = CL.IdConsulat)  as \"Consulat\"  "
                        + " , CL.Data as \"Data\"  "
                        + " , (Select ZiSaptamina FROM TemplateZi WHERE  TemplateZi.IDTemplate = CL.IDTemplate) as \"Zi Saptamina\" "
                        + " , (Select Denumirea From Services Where IdService = (Select IDService FROM TemplateZi WHERE  TemplateZi.IDTemplate = CL.IDTemplate)) as \"Service\" "
                        + " , (Select StartHour From TemplateZi WHERE  TemplateZi.IDTemplate = CL.IDTemplate) as \"Start Hour\" "
                        + " , (Select  EndHour From TemplateZi WHERE  TemplateZi.IDTemplate = CL.IDTemplate) as \"End Hour\" "
                        + " , CL.locuriLibere as \"Locuri Libere\" "
                        + " , CL.StatusAprob as \"Status Aprobare\" "
                        
                        + " FROM Calendar as CL "
                        + " WHERE CL.IdConsulat = " + idConsulat.ToString()
                        + (dataFrom > emptyData ? " AND CL.Data >= @dateFrom " : string.Empty)
                        + (dataTo > emptyData ? " AND CL.Data <= @dateTo " : string.Empty)
                        + " ORDER BY CL.Data ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@dateFrom", dataFrom);
                parameters.Add("@dateTo", dataTo);

                result = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }
    
    public bool UpdateCalendarByID(int idCalendar, DateTime dataIdCalendar, int locuriLibere, bool statusAprob)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "UPDATE Calendar SET "
                        + " Data = @dataCalendarului "
                        + " , locuriLibere = " + locuriLibere + " "
                        + " , StatusAprob = @statusAprobare "
                        + " WHERE IdCalendar = " + idCalendar;

                Hashtable parameters = new Hashtable();
                parameters.Add( "@dataCalendarului", dataIdCalendar.Date);
                parameters.Add("@statusAprobare", statusAprob);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool DeleteCalendarByID(int idCalendar)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "Delete From Calendar "
                        + " WHERE IdCalendar = " + idCalendar;

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DateTime GetMaxCalendarDataRegistred(int idConsulat, int idService)
    {
        DateTime result = DateTime.Now;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string maxDataregstredQuery = "SELECT MAX(Data) from Calendar Where "
                + " idConsulat = " + idConsulat
                + " AND IdTemplate in (SELECT IdTemplate FROM TemplateZi WHERE TemplateZi.IdConsulat = " + idConsulat + " AND IdService = " + idService + " ) ";

                DataTable maxData = mDataBridge.ExecuteQuery(maxDataregstredQuery);
                mLastError = mDataBridge.LastError;

                if (maxData != null && maxData.Rows.Count == 1 && !maxData.Rows[0][0].ToString().Equals(string.Empty))
                {
                    result = (DateTime)maxData.Rows[0][0];
                }
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool FillCalendarFromTemplate(int idConsulat, int dayCount, int serviceID)
    {
        bool result = false;

        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                DateTime maxDataRegistred = GetMaxCalendarDataRegistred(idConsulat, serviceID);              

                for (int i = 1; i < dayCount + 1; i++)
                {
                    DateTime currentDate = maxDataRegistred.AddDays(i);
                    int currentWeekDay = (int)currentDate.DayOfWeek;


                    string querySarbatori = "Select * From Sarbatori "
                                        + " WHERE idConsulat = " + idConsulat 
                                        + " AND Data = @currentData ";

                    Hashtable parameters = new Hashtable();
                    parameters.Add("@currentData", currentDate);
                    parameters.Add("@statusAprobare", false);

                    DataTable holyDay = mDataBridge.ExecuteQuery(querySarbatori, parameters);
                    mLastError = mDataBridge.LastError;

                    if (holyDay.Rows.Count == 0 && mDataBridge.Connect())
                    {
                        string queryi = "Insert INTO Calendar (IdConsulat, Data, IdTemplate, LocuriLibere, StatusAprob)  "
                           + " Select "
                           + " " + idConsulat + " "
                           + " , @currentData "
                           + " , IdTemplate "
                           + " , MaxPerson "
                           + " , @statusAprobare "
                           + " FROM TemplateZi "
                           + " WHERE ZiSaptamina = " + currentWeekDay
                           + " AND IdService = " + serviceID
                           + " AND IdConsulat = " + idConsulat;

                        Hashtable parameters2 = new Hashtable();
                        parameters2.Add("@currentData", currentDate);
                        parameters2.Add("@statusAprobare", false);

                        result = mDataBridge.ExecuteNonQuery(queryi, parameters2);
                        mLastError = mDataBridge.LastError;
                    }
                    else
                    {
                        int workHours = 0;
                        int.TryParse(holyDay.Rows[0]["WorkHours"].ToString(), out workHours);

                        DateTime emptyDate = new DateTime(1900,1,1);

                        DateTime holyDayStartWorkHour = new DateTime(1900,1,1);
                        DateTime.TryParse(holyDay.Rows[0]["WorkTimeFrom"].ToString(), out holyDayStartWorkHour);

                        DateTime holyDayEndWorkHour = new DateTime(1900, 1, 1);
                        DateTime.TryParse(holyDay.Rows[0]["WorkTimeTo"].ToString(), out holyDayEndWorkHour);

                        if (workHours == 0 && mDataBridge.Connect())
                        {
                            string query = "Insert INTO Calendar (IdConsulat, Data, IdTemplate, LocuriLibere, StatusAprob)  "
                               + " Select "
                               + " " + idConsulat + " "
                               + " ,@currentData "
                               + " , IdTemplate "
                               + " , MaxPerson "
                               + " , @statusAprobare "
                               + " FROM TemplateZi "
                               + " WHERE ZiSaptamina = " + currentWeekDay
                               + " AND IdService = " + serviceID
                               + " AND IdConsulat = " + idConsulat;

                            Hashtable parameters1 = new Hashtable();
                            parameters1.Add("@currentData", currentDate);
                            parameters1.Add("@statusAprobare", false);

                            result = mDataBridge.ExecuteNonQuery(query, parameters1);
                            mLastError = mDataBridge.LastError;
                        }
                        else
                        {
                            if (holyDayStartWorkHour != emptyDate && holyDayEndWorkHour != emptyDate && mDataBridge.Connect())
                            {
                                string query = "Insert INTO Calendar (IdConsulat, Data, IdTemplate, LocuriLibere, StatusAprob)  "
                                    + " Select "
                                    + " " + idConsulat + " "
                                    + " ,@currentData "
                                    + " , IdTemplate "
                                    + " , MaxPerson "
                                    + " , @statusAprobare "
                                    + " FROM TemplateZi "
                                    + " WHERE ZiSaptamina = " + currentWeekDay
                                    + " AND IdService = " + serviceID
                                    + " AND IdConsulat = " + idConsulat
                                    + " AND StartHour >= @HolyDayStartH "
                                    + " AND EndHour <= @HolyDayEndH ";

                                Hashtable parameters1 = new Hashtable();
                                parameters1.Add("@currentData", currentDate);
                                parameters1.Add("@statusAprobare", false);
                                parameters1.Add("@HolyDayStartH", holyDayStartWorkHour.ToString("HH:mm"));
                                parameters1.Add("@HolyDayEndH", holyDayEndWorkHour.ToString("HH:mm"));

                                result = mDataBridge.ExecuteNonQuery(query, parameters1);
                                mLastError = mDataBridge.LastError;
                            }
                        } 
                    }
                }               
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool InsertNewRowInCalendar(int idConsulat, int iDTemplate, DateTime insertData)
    {
        bool result = false;

        mLastError = string.Empty;

        try
        {
            DataTable templateDT = GetTemplateZiByID(iDTemplate);

            if (templateDT != null && templateDT.Rows.Count == 1)
            {
                if (mDataBridge.Connect() == true)
                {
                    int maxPerson = (int)templateDT.Rows[0]["MaxPerson"];
                    TimeSpan templateZiStartHour = (TimeSpan)templateDT.Rows[0]["StartHour"];
                    TimeSpan templateZiEndHour = (TimeSpan)templateDT.Rows[0]["EndHour"];

                    string querySarbatori = "Select * From Sarbatori "
                                        + " WHERE idConsulat = " + idConsulat
                                        + " AND Data = @currentData ";

                    Hashtable parameters = new Hashtable();
                    parameters.Add("@currentData", insertData);
                    parameters.Add("@statusAprobare", false);

                    DataTable holyDay = mDataBridge.ExecuteQuery(querySarbatori, parameters);
                    mLastError = mDataBridge.LastError;


                    if (holyDay.Rows.Count == 0)
                    {
                        string query = "Insert INTO Calendar (IdConsulat, Data, IdTemplate, LocuriLibere, StatusAprob)  "
                           + " Values ( "
                           + " " + idConsulat + " "
                           + " ,@currentData "
                           + " , " + iDTemplate + " "
                           + " , " + maxPerson + " "
                           + " , @statusAprobare "
                           + " ) ";

                        Hashtable parameters1 = new Hashtable();
                        parameters1.Add("@currentData", insertData);
                        parameters1.Add("@statusAprobare", false);

                        result = mDataBridge.ExecuteNonQuery(query, parameters1);
                        mLastError = mDataBridge.LastError;
                    }
                    else
                    {
                        int workHours = 0;
                        int.TryParse(holyDay.Rows[0]["WorkHours"].ToString(), out workHours);

                        TimeSpan holyDayStartWorkHour = (TimeSpan)holyDay.Rows[0]["WorkTimeFrom"];
                        TimeSpan holyDayEndWorkHour = (TimeSpan)holyDay.Rows[0]["WorkTimeTo"];

                        if (workHours == 0 || (holyDayStartWorkHour <= templateZiStartHour && holyDayEndWorkHour >= templateZiEndHour))
                        {
                            string query = "Insert INTO Calendar (IdConsulat, Data, IdTemplate, LocuriLibere, StatusAprob)  "
                           + " Values ( "
                           + " " + idConsulat + " "
                           + " ,@currentData "
                           + " , " + iDTemplate + " "
                           + " , " + maxPerson + " "
                           + " , @statusAprobare "
                           + " ) ";

                            Hashtable parameters1 = new Hashtable();
                            parameters1.Add("@currentData", insertData);
                            parameters1.Add("@statusAprobare", false);

                            result = mDataBridge.ExecuteNonQuery(query, parameters1);
                            mLastError = mDataBridge.LastError;
                        }
                    }
                }
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }    

    #endregion CALENDAR

    #region Sarbatori

    public DataTable GetSarbatoriList(int idConsulat)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT "
                        + " Sarbatori.Data as \"Data\" "
                        + " , Sarbatori.IdConsulat as \"ID Consulat\" "
                        + " , (Select Denumire From Consulate Where Consulate.IdConsulat = Sarbatori.IdConsulat) as \"Consulat\" " 
                        + " , Sarbatori.WorkTimeFrom as \"Work Time From\" "
                        + " , Sarbatori.WorkTimeTo as \"Work Time To\" "
                        + " , Sarbatori.WorkHours as \"Work Hours\"  "
                        + " FROM Sarbatori "
                        + " Where Sarbatori.IdConsulat = " + idConsulat.ToString()
                        + "ORDER BY \"Data\" ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool UpdateHolyDay(int idConsulat, DateTime inputData, string startHour, string endHour, int workHours)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "UPDATE Sarbatori SET "
                        + " idConsulat = " + idConsulat.ToString() + " "
                        + " , WorkTimeFrom = '" + startHour + "' "
                        + " , WorkTimeTo = '" + endHour + "' "
                        + " , WorkHours = " + workHours + " "
                        + " WHERE Sarbatori.Data = @inputData ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@inputData", inputData.Date);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool AddNewHolyDay(int idConsulat, DateTime inputData, string startHour, string endHour, int workHours)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "Insert Into Sarbatori (idConsulat, Sarbatori.Data, WorkTimeFrom, WorkTimeTo, WorkHours) "
                        + " Values ( "
                        + "  " + idConsulat.ToString() + " "
                        + " ,  @inputData "
                        + " , '" + startHour + "' "
                        + " , '" + endHour + "' "
                        + " ,  " + workHours + " ) ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@inputData", inputData.Date);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool DeleteHolyDay(int idConsulat, DateTime inputData)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "Delete From Sarbatori "                        
                        + " WHERE Sarbatori.Data = @inputData "
                        + " AND idConsulat = " + idConsulat.ToString() + " ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@inputData", inputData.Date);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    #endregion Sarbatori

    #region Classifier

    public DataTable GetClassifierByTypeID(int typeID)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = @"SELECT Classifiers.Code "
                    + ", Classifiers.Name "
                    + " FROM Classifiers as Classifiers "
                    + " WHERE Classifiers.TypeID = " + typeID + " "
                    + " OR Classifiers.Code = 0 "
                    + " ORDER BY Classifiers.Name ASC";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetSexTypes(int typeID, int languageID)
    {
        DataTable result = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = @"SELECT Classifiers.Code "
                    + ", Classifiers.Name "
                    + " FROM Classifiers as Classifiers "
                    + " WHERE Classifiers.TypeID = " + typeID + " "
                    + " AND Classifiers.ParentID = " + languageID
                    + " OR Classifiers.Code = 0 "
                    + " ORDER BY Classifiers.Name ASC";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    #endregion Classifier

    #region RegForm
    
    public bool AddNewSolicitant(int parentID, string nameString, string prenumeString, string oldNameString
        , string pasaportString, DateTime DataNastere, int countryGenezID, string locNastereString, int sexID, string cetatenieString, string numeTataString, string numeMamaString
        , string domiciliuString, int countryDomID, int judetDomID, string telefonString, string emailString, string passwordString, DateTime dataExpirariiPasap, int rudenieID
        , string cnp, DateTime dataEliberarePasaport, string eliberatDe)
    {
        bool result = false;
        mLastError = string.Empty;

        DateTime emptyDate = DateTime.MinValue;
       
        string encryptPassword = Utils.GetMd5Hash(passwordString);
            try
            {
                if (mDataBridge.Connect() == true)
                {
                    Guid guid_refNumber = new Guid();
                    guid_refNumber = Guid.NewGuid();
                    string refNumber = guid_refNumber.ToString();
                    bool isActive = false;

                    string query = "INSERT INTO Solicitanti (ParentID, Nume, Prenume, NumeAnterior, Pasaport, DataNastere, TaraNastereID, LocNastere, SexID, Cetatenie, NumeTATA, NumeMAMA, Domiciliu, TaraDomiciliuID, JudetDomiciliuID, Telefon, Email, Parola, DataExpPasaport, RudenieID, refNumber, Cnp, DataEmiterePasaport, isActive, EmitentPasaport)"
                    + " Values ("+parentID+ " "
                            + " ,'" + nameString + "' "
                            + " , '" + prenumeString + "' "
                            + " , '" + oldNameString + "' "
                            + " , '" + pasaportString + "' "
                            + (emptyDate.Equals(DataNastere) ? ", NULL " : " , @dataNasterii " )
                            + " , " + countryGenezID + " "
                            + " , '" + locNastereString + "' "
                            + " , " + sexID + " "
                            + " , '" + cetatenieString + "' "
                            + " , '" + numeTataString + "' "
                            + " , '" + numeMamaString + "' "
                            + " , '" + domiciliuString + "' "
                            + " , " + countryDomID + " "
                            + " , " + judetDomID + " "
                            + " , '" + telefonString + "' "
                            + " , '" + emailString + "' "
                            + " , '" + encryptPassword + "' "
                            + (emptyDate.Equals(dataExpirariiPasap) ? ", NULL " : " , @dataExpPasaportString " )
                            + " , " + rudenieID + " "
                            + " , '" + refNumber + "' "
                            + " , '" + cnp + "' "
                            + (emptyDate.Equals(dataEliberarePasaport) ? ", NULL " : " , @dataEliberarePasaport1 ")
                            + " , " + Convert.ToInt32(isActive).ToString() + " "
                            + " , '" + eliberatDe + "' "
                            + " )";

                    Hashtable parameters = new Hashtable();
                    if (!emptyDate.Equals(DataNastere)) parameters.Add("@dataNasterii", DataNastere.Date);
                    if (!emptyDate.Equals(dataExpirariiPasap)) parameters.Add("@dataExpPasaportString", dataExpirariiPasap.Date);
                    if(!emptyDate.Equals(dataEliberarePasaport)) parameters.Add("@dataEliberarePasaport1", dataEliberarePasaport.Date);

                    result = mDataBridge.ExecuteNonQuery(query, parameters);
                    mLastError = mDataBridge.LastError;
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }
            finally
            {
                mDataBridge.Disconnect();
            }
      

        return result;
    }

    public bool UpdateSolicitantiPersonalData(int userID, int parentID, string nameString, string prenumeString, string numeAnteriorString, string pasaportString
        , DateTime dataNastere, int taraNastereID, string locNastereString, string cnp, int sexID, string cetateniaString, string numeTataString, string numeMamaString, string domiciliuString, int taraDomiciliuID, int judetDomiciliuID, string telefonString, string emailString, DateTime dataExpirariiPasaport, int rudenieID, DateTime dataEliberariiPasaport, string eliberatDe)
    {
        bool result = false;
        mLastError = string.Empty;

        DateTime emptyDate = DateTime.MinValue;
       
        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "UPDATE SOLICITANTI SET "
                        + " Nume = '" + nameString + "' "
                        + " , Prenume = '" + prenumeString + "' "
                        + " , NumeAnterior = '" + numeAnteriorString + "' "
                        + " , Pasaport = '" + pasaportString + "' "
                        + " , DataNastere = " + (emptyDate.Equals(dataNastere) ? "NULL" : " @dataNastere ")
                        + " , TaraNastereID = " + taraNastereID+" "
                        + " , LocNastere = '" + locNastereString + "' "
                        + " , SexID = " + sexID + " "
                        + " , Cetatenie = '" + cetateniaString + "'"
                        + ", NumeTata = '" + numeTataString + "' "
                        + ", NumeMama = '" + numeMamaString + "' "
                        + ", Domiciliu = '" + domiciliuString + "' "
                        + ", TaraDomiciliuID = " + taraDomiciliuID + " "
                        + ", JudetDomiciliuID = " + judetDomiciliuID + " "
                        + ", Telefon = '"+ telefonString+"' "
                        + ", Email = '" + emailString + "' "
                        + ", DataExpPasaport = " + (emptyDate.Equals(dataExpirariiPasaport) ? "NULL" : " @dataExpirariiPasaport ")
                        + ", DataEmiterePasaport =  " + (emptyDate.Equals(dataEliberariiPasaport) ? "NULL" : " @dataEmiterePasaport ") 
                        + ", RudenieID = " + rudenieID + " "
                        + ", EmitentPasaport = '" + eliberatDe + "' "
                        + ", ParentID = " + parentID
                        + ", Cnp = '" + cnp + "' " 

                        + " WHERE SOLICITANTI.Id = " +userID + "";

                Hashtable parameters = new Hashtable();
                if (!emptyDate.Equals(dataNastere)) parameters.Add("@dataNastere", dataNastere.Date);
                if (!emptyDate.Equals(dataExpirariiPasaport)) parameters.Add("@dataExpirariiPasaport", dataExpirariiPasaport.Date);
                if (!emptyDate.Equals(dataEliberariiPasaport)) parameters.Add("@dataEmiterePasaport", dataEliberariiPasaport.Date);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool UpdatePasswordRegForm(int userID, string passwordString)
    {
        bool result = false;
        mLastError = string.Empty;
        string encryptPassword = Utils.GetMd5Hash(passwordString);
        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "UPDATE SOLICITANTI SET "
                        + " Parola = '" + encryptPassword + "' "
                        + " WHERE SOLICITANTI.Id = " + userID
                        + " ";

                //Hashtable parameters = new Hashtable();
                //parameters.Add("@inputData", inputData.Date);

                result = mDataBridge.ExecuteNonQuery(query);//, parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetSolicitantiByUserID(int userID)
    {
        DataTable resultTable = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = @"SELECT * "
                    + " FROM Solicitanti "
                    + " WHERE ID = " + userID
                    + " ";

                resultTable = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }
        return resultTable;
    }

    public int GetSolicitantByKey(string nameString, string prenumeString, DateTime dateNastere, string locNastereString)
    {
        int result = 0;

        DataTable resultTable = new DataTable();
        mLastError = string.Empty;

        try
        {
            DateTime emptyDate = new DateTime(1900, 1, 1);

            if (mDataBridge.Connect() == true)
            {
                string query = @"SELECT * "
                    + " FROM Solicitanti "
                    + " WHERE Nume = @nameString "
                    + " AND Prenume = @prenumeString "
                    + (dateNastere != emptyDate ? " AND DataNastere = @dataNaster " : string.Empty)
                    + " AND LocNastere = @locNastereString ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@dataNaster", dateNastere.Date);
                parameters.Add("@nameString", nameString);
                parameters.Add("@prenumeString", prenumeString);
                parameters.Add("@locNastereString", locNastereString);

                resultTable = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;

                if (resultTable != null && resultTable.Rows.Count > 0)
                {
                    result = (int)resultTable.Rows[0]["ID"];
                }
                else
                {
                    query = @"SELECT * "
                                       + " FROM Solicitanti "
                                       + " WHERE Nume = @nameString "
                                       + " AND Prenume = @prenumeString "
                                       + (dateNastere != emptyDate ? " AND DataNastere = @dataNaster " : string.Empty)
                                       + " AND LocNastere = @locNastereString ";

                    string nameString_WithOoutDiacrit = Utils.ReplaceAcronim(nameString);
                    string prenumeString_WithOoutDiacrit = Utils.ReplaceAcronim(prenumeString);
                    string locNastereString_WithOoutDiacrit = Utils.ReplaceAcronim(locNastereString);

                    Hashtable parameters2 = new Hashtable();
                    parameters2.Add("@dataNaster", dateNastere.Date);
                    parameters2.Add("@nameString", nameString_WithOoutDiacrit);
                    parameters2.Add("@prenumeString", prenumeString_WithOoutDiacrit);
                    parameters2.Add("@locNastereString", locNastereString_WithOoutDiacrit);

                    resultTable = mDataBridge.ExecuteQuery(query, parameters2);
                    mLastError = mDataBridge.LastError;

                    if (resultTable != null && resultTable.Rows.Count > 0)
                    {
                        result = (int)resultTable.Rows[0]["ID"];
                    }
                }
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }
        return result;
    }

    public bool CheckSolicitantiByGUID(string stringGUID)
    {
        bool result = false;

        DataTable resultTable = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = @"SELECT * "
                    + " FROM Solicitanti "
                    + " WHERE refNumber = '" + stringGUID + "' "
                    + " ";
                
                resultTable = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (resultTable != null && resultTable.Rows.Count == 1)
                {
                    bool isActive = true;
                    string queryUpdate = "UPDATE SOLICITANTI SET "
                        + " refNumber = ''"
                        + " , isActive = " + Convert.ToInt32(isActive).ToString() + " "
                        + " WHERE SOLICITANTI.refNumber = '" + stringGUID + "' ";

                    result = mDataBridge.ExecuteNonQuery(queryUpdate);
                    mLastError = mDataBridge.LastError;
                }
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetFamilyListByUserID(int parentUserID, bool includeParentInResult)
    {
        DataTable resultTable = new DataTable();
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = @"SELECT ID as Code"
                    + ", Nume +' '+ Prenume  as Name "
                    + ", DataNastere as DataNastere "
                    + ", Nume "
                    + ", Prenume "
                    + ", ParentID "
                    + " FROM Solicitanti "
                    + " WHERE ParentID = " + parentUserID
                    + (includeParentInResult ? " OR ID = " + parentUserID : string.Empty)
                    + " Order BY ID ";

                resultTable = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;               
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }
        return resultTable;
    }

    #endregion RegForm
    
    public DataTable ChekIfExistAnyRegistrations(int idConsulat, int idSolicitant)
    {
        DataTable result = new DataTable();

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT PROG_ID AS \"Nr. programare\""
                    + " ,(Select Denumire From Consulate Where idConsulat = Programare.idConsulat) as \"Denumirea Consulatului\" "
                    + " ,(Select Adresa From Consulate Where idConsulat = Programare.idConsulat) as \"Adresa Consulatului\" "
                    + " ,DataSolicitarii as \"Data Solicitarii\" "
                    + " ,DataProgramarii as \"Data Programarii\" "
                    + " ,OraProgramarii as \"Ora Programarii\" "
                    + " , (Select Denumirea From Services Where idService = Programare.idService) as \"Serviciul\" "
                    + " , DataPrel as \"Data Prelucrarii\" "

                    + " FROM Programare "
                    + " WHERE SOLICITANT_ID = " + idSolicitant
                    + " AND idConsulat = " + idConsulat
                    + " AND ( "
                    + " (DataProgramarii > @datadeazi) "
                    + " OR (DataPrel > @datadeazi) "
                    + " ) ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@datadeazi", DateTime.Now.Date);

                result = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool AddNewProgramare(int solicitant_ID, int parentID, int idConsulat, DateTime dataSolicitarii, DateTime dataProgramarii, TimeSpan oraProgramarii, int idService, int selectedIdCalendar)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = @"INSERT INTO PROGRAMARE 
                        (SOLICITANT_ID, ParentID, idConsulat, DataSolicitarii, DataProgramarii, OraProgramarii, idService, idCalendar) "
                        + " Values ("
                        + " " + solicitant_ID + " "
                        + " ," + parentID + " "
                        + " ," + idConsulat + " "
                        + " , @dataSolicitarii "
                        + " , @dataProgramarii "
                        + " , @oraProgramarii "
                        + " , " + idService + " "
                        + " , " + selectedIdCalendar + "  "
                        + " )";

                Hashtable parameters = new Hashtable();
                parameters.Add("@dataSolicitarii", dataSolicitarii);
                parameters.Add("@dataProgramarii", dataProgramarii);
                parameters.Add("@oraProgramarii", oraProgramarii);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;

                if (result)
                {
                    query = @"Update Calendar "
                        + " SET LocuriLibere = LocuriLibere - 1 "
                        + " WHERE idCalendar = " + selectedIdCalendar + " ";  

                    result = mDataBridge.ExecuteNonQuery(query, parameters);
                    mLastError = mDataBridge.LastError;
                }
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public bool AnulareProgramare(int idProgramare)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = @"Select * FROM PROGRAMARE WHERE PROG_ID =  " + idProgramare;
                DataTable programareData = mDataBridge.ExecuteQuery(query);

                if (programareData != null && programareData.Rows.Count == 1)
                {
                    query = @"Delete FROM PROGRAMARE WHERE PROG_ID =  " + idProgramare;

                    result = mDataBridge.ExecuteNonQuery(query);
                    mLastError = mDataBridge.LastError;

                    if (result)
                    {
                        query = @"Update Calendar "
                            + " SET LocuriLibere = LocuriLibere + 1 "
                            + " WHERE idCalendar = " + programareData.Rows[0]["idCalendar"].ToString() + " ";

                        result = mDataBridge.ExecuteNonQuery(query);
                        mLastError = mDataBridge.LastError;
                    }
                }
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
 
    }

    public bool AnulareCont(int idSolicitant)
    {
        bool result = false;
        mLastError = string.Empty;

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = @"UPDATE Solicitanti SET isActive = @activeStatus WHERE ID = " + idSolicitant;

                Hashtable parameters = new Hashtable();
                parameters.Add("@activeStatus", false);

                result = mDataBridge.ExecuteNonQuery(query, parameters);           
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetStatisticaInregistrarilor(int idSolicitant)
    {
        DataTable result = new DataTable();

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT PROG_ID AS \"Nr. programare\""
                    + " ,DataSolicitarii as \"Data Solicitarii\" "
                    + " ,DataProgramarii as \"Data Programarii\" "
                    + " ,OraProgramarii as \"Ora Programarii\" "
                    + " , (Select Denumirea From Services Where idService = Programare.idService) as \"Serviciul\" "
                    + " , DataPrel as \"Data Prelucrarii\" "
                    +" , (Select Solicitanti.Nume + ' ' + Solicitanti.Prenume FROM Solicitanti WHERE Solicitanti.ID = Solicitant_ID) AS \"Nume Prenume\" "
                    + ", CASE WHEN DataProgramarii < @currentDate THEN @falceParameter ELSE @trueParameter END  as \"ButtonState\" " 
                    + " FROM Programare "
                    + " WHERE SOLICITANT_ID IN (Select Solicitanti.ID FROM Solicitanti WHERE Solicitanti.ID="+ idSolicitant+" OR Solicitanti.ParentID="+ idSolicitant+") "
                    + " ORDER BY DataProgramarii DESC ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@currentDate", DateTime.Now.Date);
                parameters.Add("@trueParameter", true);
                parameters.Add("@falceParameter", false);

                result = mDataBridge.ExecuteQuery(query,parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetAviableServiceIntervalForOneDay(int idConsulat, int idService, DateTime dataSolicitarii)
    {
        DataTable result = new DataTable();

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = @"SELECT * FROM Calendar
                                JOIN TemplateZi ON Calendar.IdConsulat = TemplateZi.IdConsulat
                                AND Calendar.IdTemplate = TemplateZi.IdTemplate
                                WHERE 
                                Calendar.IdConsulat = " + idConsulat
                            + " AND IdService = " + idService
                            + " AND [DATA] = @maxDisplayData "
                            + " AND StatusAprob = 1 "
                            + " AND LocuriLibere > = 0 ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@maxDisplayData", dataSolicitarii.Date);

                DataTable resultTable = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;

                if (resultTable != null && resultTable.Rows.Count > 0)
                {
                    for (int i = 0; i < resultTable.Rows.Count; i++)
                    {
                        DateTime dataCol = (DateTime)resultTable.Rows[i]["Data"];

                        string columnName = dataCol.ToString("dd.MM.yyyy");
                        if (!result.Columns.Contains(columnName))
                            result.Columns.Add(columnName, typeof(string));

                        bool wasInserted = false;
                        for (int j = 0; j < result.Rows.Count && !wasInserted; j++)
                        {
                            if (result.Rows[j][columnName].ToString().Equals(string.Empty))
                            {
                                result.Rows[j][columnName] = resultTable.Rows[i]["StartHour"].ToString() + " - " + resultTable.Rows[i]["EndHour"].ToString();
                                wasInserted = true;
                            }
                        }
                        if (!wasInserted)
                        {
                            result.Rows.Add();
                            result.Rows[result.Rows.Count - 1][columnName] = resultTable.Rows[i]["StartHour"].ToString() + " - " + resultTable.Rows[i]["EndHour"].ToString();
                        }
                    }
                }
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetAviableServiceInterval(int idConsulat, int idService, DateTime dataSolicitarii, int minRent)
    {
        DataTable result = new DataTable();

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = @"SELECT * FROM Calendar
                                JOIN TemplateZi ON Calendar.IdConsulat = TemplateZi.IdConsulat
                                AND Calendar.IdTemplate = TemplateZi.IdTemplate
                                WHERE 
                                Calendar.IdConsulat = " + idConsulat 
                            + " AND IdService = " + idService
                            + " AND [DATA] = @maxDisplayData "
                            + " AND StatusAprob = 1 "
                            + " AND LocuriLibere > = " + minRent;

                Hashtable parameters = new Hashtable();
                parameters.Add("@maxDisplayData", dataSolicitarii.Date);

                DataTable resultTable = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;

                if (resultTable != null && resultTable.Rows.Count > 0)
                {
                    string columnName = dataSolicitarii.Date.ToString("dd.MM.yyyy");
                    string calendarID = "idCalendar";

                    if (!result.Columns.Contains(columnName))
                    {
                        result.Columns.Add(columnName, typeof(string));
                        result.Columns.Add(calendarID, typeof(string));
                    }


                     for (int i = 0; i < resultTable.Rows.Count; i++)
                     {
                         bool wasInserted = false;
                         for (int j = 0; j < result.Rows.Count && !wasInserted; j++)
                         {
                             if (result.Rows[j][columnName].ToString().Equals(string.Empty))
                             {
                                 result.Rows[j][columnName] = resultTable.Rows[i]["StartHour"].ToString();// +" - " + resultTable.Rows[i]["EndHour"].ToString();
                                 result.Rows[j][calendarID] = resultTable.Rows[i][calendarID].ToString();
                                 wasInserted = true;
                             }
                         }
                         if (!wasInserted)
                         {
                             result.Rows.Add();
                             result.Rows[result.Rows.Count - 1][columnName] = resultTable.Rows[i]["StartHour"].ToString();// +" - " + resultTable.Rows[i]["EndHour"].ToString();
                             result.Rows[result.Rows.Count - 1][calendarID] = resultTable.Rows[i][calendarID].ToString();
                         }
                     }                  
                }
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetRegistredPersonByDay(int idConsulat, DateTime dataProgramarii, TimeSpan inputTime, bool useTimeFilter, int idService, bool useServiceFilter)
    {
        DataTable result = new DataTable();

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT PROG_ID "
                            + " ,SOLICITANT_ID "
                            + " , (SELECT Nume FROM Solicitanti WHERE Solicitanti.ID = SOLICITANT_ID) as \"NUME\" "
                            + " , (SELECT Prenume FROM Solicitanti WHERE Solicitanti.ID = SOLICITANT_ID) as \"PRENUME\"       "
                            + " , (SELECT NumeAnterior FROM Solicitanti WHERE Solicitanti.ID = SOLICITANT_ID) as \"NUME ANTERIOR\" "
                            + " , (SELECT DataNastere FROM Solicitanti WHERE Solicitanti.ID = SOLICITANT_ID) as \"DATA NASTERE\"   "
                            + " , (SELECT Pasaport FROM Solicitanti WHERE Solicitanti.ID = SOLICITANT_ID) as \"PASAPORT\"           "
                            + " ,DataSolicitarii "
                            + " ,OraProgramarii "
                            + " ,(SELECT Denumirea FROM Services WHERE Services.IdService = PROGRAMARE.idService ) as \"Serviciu\" "

                            + " FROM PROGRAMARE "
                            + " WHERE DataProgramarii = @Data "
                            + " AND idConsulat =  " + idConsulat
                            + (useTimeFilter ? " AND OraProgramarii = @oraProg " : string.Empty)
                            + (useServiceFilter ? " AND idService = " + idService : string.Empty);

                Hashtable parameters = new Hashtable();
                parameters.Add("@Data", dataProgramarii.Date);
                parameters.Add("@oraProg", inputTime);

                result = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;               
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return result;
    }

    public DataTable GetProgramareID(int editUserID, int parentID, int idConsulat, DateTime dataSolicitarii, DateTime dataProgramarii, TimeSpan oraProgramarii, int idService)
    {
        DataTable resultTable = new DataTable();

        try
        {
            if (mDataBridge.Connect() == true)
            {
                string query = "SELECT * "
                    + " , (Select Denumire From Consulate Where idConsulat = Programare.idConsulat) as \"Denumirea Consulatului\" "
                    + " , (Select Adresa From Consulate Where idConsulat = Programare.idConsulat) as \"Adresa Consulatului\" "
                    + " , (Select Denumirea From Services Where idService = Programare.idService) as \"Serviciul\" "
                    + " FROM Programare "
                    + " WHERE SOLICITANT_ID = " + editUserID + " "
                    + " AND ParentID = " + parentID + " "
                    + " AND idConsulat = " + idConsulat + " "
                    + " AND DataSolicitarii = @dataSolicitarii "
                    + " AND DataProgramarii = @dataProgramarii "
                    + " AND OraProgramarii = @oraProgramarii "
                    + " AND idService = " + idService + " "
                    + " ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@dataSolicitarii", dataSolicitarii.Date);
                parameters.Add("@dataProgramarii", dataProgramarii.Date);
                parameters.Add("@oraProgramarii", oraProgramarii);

                resultTable = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
        }
        catch (Exception exception)
        {
            mLastError += "Error using DataBridge. " + exception.Message;
        }
        finally
        {
            mDataBridge.Disconnect();
        }

        return resultTable;
    }
}